﻿/*****************************************************
 * Author：Jin.Qin
 * Create Date：2016-03-27
 * Description：为SQLite专门编写的数据访问处理器，沿袭JIN之前编写的标准的DataCommand用法
 * 
 * **************************************************/
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using YZ.Utility;

namespace YZ.Labor.DataAccess
{
    public class DataCommand
    {
        const int TIMEOUT = 60;
        const string INIT_QUERYCONDITION_STRING = " WHERE 1=1 ";

        private string m_CommandText = string.Empty;
        private List<SQLiteParameter> m_DbParameterList;

        private SQLiteConnection m_Conn = null;
        private string m_ConnStr = null;
        public static readonly string Key = "yzw54321!@#￥%";
        /// <summary>
        /// 构造函数需要传入SQL语句
        /// </summary>
        /// <param name="commandText"></param>
        public DataCommand(string commandText)
        {
            m_ConnStr = ConfigurationManager.AppSettings["DBConnString"];
            if (string.IsNullOrWhiteSpace(m_ConnStr))
            {
                throw new Exception("Please set your DBConnString in app.config!");
            }
            m_ConnStr = CryptoManager.Decrypt(m_ConnStr.Trim());

            //add by tom on 2016-05-05 for启动相对路径
            string currentPath = AppDomain.CurrentDomain.BaseDirectory;
            m_ConnStr = m_ConnStr.Replace("|path|", currentPath);

            m_CommandText = commandText;
            Regex regex = new Regex(@"@\w*", RegexOptions.IgnoreCase);
            MatchCollection matches = regex.Matches(m_CommandText.Trim());
            m_DbParameterList = new List<SQLiteParameter>();
            if (matches != null && matches.Count > 0)
            {
                foreach (Match match in matches)
                {
                    if (!m_DbParameterList.Exists(f => f.ParameterName.Trim().ToLower() == match.Value.Trim().ToLower()))
                    {
                        SQLiteParameter p = new SQLiteParameter();
                        p.ParameterName = match.Value;
                        m_DbParameterList.Add(p);
                    }
                }
            }
        }

        private DataCommand() { }

        /// <summary>
        /// 获取一个链接
        /// </summary>
        /// <returns></returns>
        public SQLiteConnection GetConnection()
        {
            if (m_Conn == null)
            {
                m_Conn = new SQLiteConnection(m_ConnStr);
            }
            m_Conn.SetPassword(Key);
            return m_Conn;
        }

        /// <summary>
        /// 获取或者设置DataCommand的SQL语句
        /// </summary>
        public string CommandText
        {
            get
            {
                return m_CommandText;
            }
            set
            {
                m_CommandText = value;
            }
        }


        #region 设置参数
        /// <summary>
        /// 手动设置参数
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        public void SetParameter(string paramName, DbType dbType, object value)
        {
            SetParameter(paramName, dbType, value, 0, ParameterDirection.Input);
        }

        /// <summary>
        /// 手动设置参数
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        /// <param name="size"></param>
        public void SetParameter(string paramName, DbType dbType, object value, int size)
        {
            SetParameter(paramName, dbType, value, size, ParameterDirection.Input);
        }


        /// <summary>
        /// 手动设置参数
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        /// <param name="size"></param>
        /// <param name="direction"></param>
        public void SetParameter(string paramName, DbType dbType, object value, int size, ParameterDirection direction)
        {
            if (!paramName.StartsWith("@"))
            {
                paramName = "@" + paramName;
            }
            SQLiteParameter p = m_DbParameterList.Find(f => f.ParameterName.ToLower() == paramName.ToLower().Trim());
            if (p == null)
            {
                throw new Exception(string.Format("SQL语句中缺少参数定义：{0}；SQL语句:{1}", paramName, m_CommandText));
            }
            p.DbType = dbType;
            p.Value = ConvertDbValue(value);
            p.Size = size;
            p.Direction = direction;
        }


        /// <summary>
        /// 直接设置实体的参数，系统将根据类的属性名以及属性数据类型，自动Mapping到SQL中的参数上去
        /// 如果有手动设置参数，则手动设置参数的优先，后面自动匹配的则会跳过已手动设置的参数
        /// </summary>
        /// <typeparam name="T">实体泛型</typeparam>
        /// <param name="entity">实体对象</param>
        /// <param name="manuSetParameter">使用手动设置参数:SetParameter，手动设置参数的优先，后面自动匹配的则会跳过已手动设置的参数</param>
        public void SetParameter<T>(T entity, Action<DataCommand, T> manuSetParameter = null) where T : class
        {
            //手动设置参数的优先，后面自动匹配的则会跳过已手动设置的参数
            if (manuSetParameter != null)
            {
                manuSetParameter(this, entity);
            }

            PropertyInfo[] propArray = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (SQLiteParameter param in m_DbParameterList)
            {
                bool existParam = false;
                foreach (PropertyInfo prop in propArray)
                {
                    string tempName = "@" + prop.Name.ToLower();
                    if (tempName == param.ParameterName.ToLower())
                    {
                        param.DbType = ConvertDbType(prop.PropertyType);
                        param.Direction = ParameterDirection.Input;
                        param.Value = ConvertDbValue(Invoker.PropertyGet(entity, prop.Name));
                        existParam = true;
                        break;
                    }
                }
                if (!existParam)
                {
                    throw new Exception(string.Format("数据对象{0}缺少属性{1}，对应sql中的参数@{1}", typeof(T).Name, param.ParameterName));
                }
            }
        }

        #endregion 设置参数End

        #region 值类型及赋值转换
        private object ConvertDbValue(object value)
        {
            if (value == null || value == DBNull.Value)
            {
                return DBNull.Value;
            }
            //枚举参数值转换为Int
            Type type = value.GetType();
            if (type.IsEnum ||
                (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>)
                    && type.GetGenericArguments() != null
                    && type.GetGenericArguments().Length == 1
                    && type.GetGenericArguments()[0].IsEnum))
            {
                return Convert.ChangeType(value, type.BaseType);
            }
            return value;
        }

        private DbType ConvertDbType(Type type)
        {
            Type baseType = type;
            //process enum
            if (type.IsEnum ||
                (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>)
                    && type.GetGenericArguments() != null
                    && type.GetGenericArguments().Length == 1
                    && type.GetGenericArguments()[0].IsEnum))
            {
                Type enumBaseType = null;
                if (type.IsEnum)
                {
                    enumBaseType = type.GetEnumUnderlyingType();
                }
                else
                {
                    enumBaseType = type.GetGenericArguments()[0];
                }

                if (enumBaseType == typeof(byte))
                {
                    return DbType.Byte;
                }
                else
                {
                    return DbType.Int32;
                }
            }
            //process generic type
            else if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                baseType = type.GetGenericArguments()[0];
            }

            //TODO:转换所有的
            switch (baseType.Name.ToLower())
            {
                case "int32":
                    return DbType.Int32;
                case "int16":
                    return DbType.Int16;
                case "int64":
                    return DbType.Int64;
                case "decimal":
                    return DbType.Decimal;
                case "single":
                    return DbType.Decimal;
                case "double":
                    return DbType.Double;
                case "datetime":
                    return DbType.DateTime;
                case "guid":
                    return DbType.Guid;
                case "boolean":
                    return DbType.Int32;
                case ("System.Byte[]"):
                    return DbType.Binary;
                case ("System.Guid"):
                    return DbType.Guid;
                case ("System.Object"):
                    return DbType.Object;
                case ("System.Boolean"):
                    return DbType.Boolean;
                default:
                    return DbType.String;
            }

        }
        #endregion



        #region DataCommand的CURD方法
        /// <summary>
        /// 对于普通CURD的DataCommand方法，提供动态构建条件的方法，如果使用的SetDynamicParameter，请在执行时使用含有dynamicParameters参数的Exeute***方法
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="condOperation"></param>
        /// <param name="parameterDbType"></param>
        /// <param name="objValue"></param>
        public void SetDynamicParameter(string fieldName, ConditionOperation condOperation, DbType parameterDbType, object objValue)
        {
            QuerySetCondition(fieldName, condOperation, parameterDbType, objValue);
        }

        private void ParseDynamicParametersForExecute(string dynamicParameters)
        {
            if (m_QueryConditionString.Contains(INIT_QUERYCONDITION_STRING))
            {
                m_QueryConditionString = m_QueryConditionString.Replace(INIT_QUERYCONDITION_STRING, "");
            }
            m_CommandText = m_CommandText.Replace(dynamicParameters, m_QueryConditionString);
        }

        private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType,
           string cmdText, int timeout, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandTimeout = timeout;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 标准方法：执行返回DataSet
        /// </summary>
        /// <returns></returns>
        public DataSet ExecuteDataSet()
        {
            SQLiteCommand cmd = GetConnection().CreateCommand();
            DataSet ds = new DataSet();
            ConnectionWrapper<DbConnection> wrapper = null;
            try
            {
                wrapper = TransactionScopeConnections.GetOpenConnection(m_ConnStr, GetConnection, true);
                PrepareCommand(cmd, wrapper.Connection, null, CommandType.Text, m_CommandText, TIMEOUT, m_DbParameterList.ToArray());
                DbDataAdapter sda = new SQLiteDataAdapter();
                sda.SelectCommand = cmd;
                sda.Fill(ds);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw new Exception("ExecuteDataSet Error:" + ex.Message);
            }
            finally
            {
                if (wrapper != null)
                {
                    wrapper.Dispose();
                }
            }
            return ds;
        }
        /// <summary>
        /// 含有动态条件的执行，如sql：WHERE CategorySysNo=@CategorySysNo #DynamicParameters#，那么DataAccess层中的代码应该如下：
        ///  cmd.SetParameter("@CategorySysNo", DbType.Int32, categorySysNo);
        ///  cmd.SetDynamicParameter("ProductStatus", ConditionOperation.In, DbType.Int32, pstatusList);
        ///  DataSet ds = cmd.ExecuteDataSet("#DynamicParameters#");
        /// </summary>
        /// <param name="dynamicParameters">动态条件的占位符，建议用#DynamicParameters#，可以自行定义，只要保持与SQL脚本中占位符一致即可</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string dynamicParameters)
        {
            ParseDynamicParametersForExecute(dynamicParameters);
            return ExecuteDataSet();
        }

        /// <summary>
        /// 标准方法：执行返回DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ExecuteDataTable()
        {
            SQLiteCommand cmd = GetConnection().CreateCommand();
            DataTable table = new DataTable();
            ConnectionWrapper<DbConnection> wrapper = null;
            try
            {
                wrapper = TransactionScopeConnections.GetOpenConnection(m_ConnStr, GetConnection, true);
                PrepareCommand(cmd, wrapper.Connection, null, CommandType.Text, m_CommandText, TIMEOUT, m_DbParameterList.ToArray());
                DbDataAdapter sda = new SQLiteDataAdapter();
                sda.SelectCommand = cmd;
                sda.Fill(table);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw new Exception("ExecuteDataTable Error:" + ex.Message);
            }
            finally
            {
                if (wrapper != null)
                {
                    wrapper.Dispose();
                }
            }
            return table;

        }

        /// <summary>
        /// 含有动态条件的执行，如sql：WHERE CategorySysNo=@CategorySysNo #DynamicParameters#，那么DataAccess层中的代码应该如下：
        ///  cmd.SetParameter("@CategorySysNo", DbType.Int32, categorySysNo);
        ///  cmd.SetDynamicParameter("ProductStatus", ConditionOperation.In, DbType.Int32, pstatusList);
        ///  DataTable dt = cmd.ExecuteDataTable("#DynamicParameters#");
        /// </summary>
        /// <param name="dynamicParameters">动态条件的占位符，建议用#DynamicParameters#，可以自行定义，只要保持与SQL脚本中占位符一致即可</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string dynamicParameters)
        {
            ParseDynamicParametersForExecute(dynamicParameters);
            return ExecuteDataTable();
        }

        /// <summary>
        /// 执行返回对象列表
        /// </summary>
        /// <typeparam name="T">实体类型，必须是可无参实例化的class</typeparam>
        /// <param name="manualMapper">可以手动mapping</param>
        /// <returns></returns>
        public List<T> ExecuteEntityList<T>(Action<DataRow, T> manualMapper = null) where T : class, new()
        {
            DataTable dt = ExecuteDataTable();

            List<T> list = new List<T>();
            if (dt != null && dt.Rows.Count > 0)
            {
                list = DataMapper.GetEntityList<T, List<T>>(dt.Rows, true, true, manualMapper);
            }
            return list;
        }

        /// <summary>
        /// 执行返回对象列表; 含有动态条件的执行，如sql：WHERE CategorySysNo=@CategorySysNo #DynamicParameters#，那么DataAccess层中的代码应该如下：
        ///  cmd.SetParameter("@CategorySysNo", DbType.Int32, categorySysNo);
        ///  cmd.SetDynamicParameter("ProductStatus", ConditionOperation.In, DbType.Int32, pstatusList);
        ///  cmd.ExecuteEntityList<T>("#DynamicParameters#");
        /// </summary> 
        /// <typeparam name="T">实体类型，必须是可无参实例化的class</typeparam>
        /// <param name="dynamicParameters"></param>
        /// <param name="manualMapper"></param>
        /// <returns></returns>
        public List<T> ExecuteEntityList<T>(string dynamicParameters, Action<DataRow, T> manualMapper = null) where T : class, new()
        {
            ParseDynamicParametersForExecute(dynamicParameters);
            return ExecuteEntityList<T>(manualMapper);
        }

        /// <summary>
        /// 标准方法：执行返回第一行的DataRow
        /// </summary>
        /// <returns></returns>
        public DataRow ExecuteDataRow()
        {
            DataTable table = ExecuteDataTable();
            if (table.Rows.Count == 0)
            {
                return null;
            }
            return table.Rows[0];
        }

        /// <summary>
        /// 执行返回第一行的DataRow;
        /// 含有动态条件的执行，如sql：WHERE CategorySysNo=@CategorySysNo #DynamicParameters#，那么DataAccess层中的代码应该如下：
        ///  cmd.SetParameter("@CategorySysNo", DbType.Int32, categorySysNo);
        ///  cmd.SetDynamicParameter("ProductStatus", ConditionOperation.In, DbType.Int32, pstatusList);
        ///  cmd.ExecuteDataRow("#DynamicParameters#");
        /// </summary>
        /// <param name="dynamicParameters"></param>
        /// <returns></returns>
        public DataRow ExecuteDataRow(string dynamicParameters)
        {
            ParseDynamicParametersForExecute(dynamicParameters);
            return ExecuteDataRow();
        }

        /// <summary>
        /// 执行返回一个实体对象，将第一行DataRow转换为实体
        /// </summary>
        /// <typeparam name="T">实体类型，必须是可无参实例化的class</typeparam>
        /// <param name="manualMapper">可以手动mapping</param>
        /// <returns></returns>
        public T ExecuteEntity<T>(Action<DataRow, T> manualMapper = null) where T : class, new()
        {
            DataRow dr = ExecuteDataRow();
            if (dr != null)
            {
                T t = DataMapper.GetEntity<T>(dr, true, true, manualMapper);
                return t;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 执行返回一个实体对象，将第一行DataRow转换为实体;
        /// 含有动态条件的执行，如sql：WHERE CategorySysNo=@CategorySysNo #DynamicParameters#，那么DataAccess层中的代码应该如下：
        ///  cmd.SetParameter("@CategorySysNo", DbType.Int32, categorySysNo);
        ///  cmd.SetDynamicParameter("ProductStatus", ConditionOperation.In, DbType.Int32, pstatusList);
        ///  cmd.ExecuteEntity<T>("#DynamicParameters#");
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dynamicParameters"></param>
        /// <param name="manualMapper"></param>
        /// <returns></returns>
        public T ExecuteEntity<T>(string dynamicParameters, Action<DataRow, T> manualMapper = null) where T : class, new()
        {
            ParseDynamicParametersForExecute(dynamicParameters);
            return ExecuteEntity<T>(manualMapper);
        }

        /// <summary>
        /// 标准方法：执行返回第一行第一列的值
        /// </summary>
        /// <returns></returns>
        public object ExecuteScalar()
        {
            SQLiteCommand cmd = GetConnection().CreateCommand();
            DataTable table = new DataTable();
            ConnectionWrapper<DbConnection> wrapper = null;
            try
            {
                wrapper = TransactionScopeConnections.GetOpenConnection(m_ConnStr, GetConnection, true);
                PrepareCommand(cmd, wrapper.Connection, null, CommandType.Text, m_CommandText, TIMEOUT, m_DbParameterList.ToArray());

                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                throw new Exception("ExecuteScalar Error:" + ex.Message);
            }
            finally
            {
                if (wrapper != null)
                {
                    wrapper.Dispose();
                }
            }


        }

        /// <summary>
        /// 执行返回第一行第一列的值,
        /// 含有动态条件的执行，如sql：WHERE CategorySysNo=@CategorySysNo #DynamicParameters#，那么DataAccess层中的代码应该如下：
        ///  cmd.SetParameter("@CategorySysNo", DbType.Int32, categorySysNo);
        ///  cmd.SetDynamicParameter("ProductStatus", ConditionOperation.In, DbType.Int32, pstatusList);
        ///  cmd.ExecuteScalar("#DynamicParameters#");
        /// </summary>
        /// <param name="dynamicParameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string dynamicParameters)
        {
            ParseDynamicParametersForExecute(dynamicParameters);
            return ExecuteScalar();
        }

        /// <summary>
        /// 执行返回第一行第一列的值，并自动转换为对应的类型，如果是泛型值且为空则会返回null
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T ExecuteScalar<T>()
        {
            object v = DataMapper.ConvertIfEnum(ExecuteScalar(), typeof(T));
            return DataConvertor.GetValue<T>(v, null, null);
        }

        /// <summary>
        /// 执行返回第一行第一列的值，并自动转换为对应的类型，如果是泛型值且为空则会返回null
        /// 含有动态条件的执行，如sql：WHERE CategorySysNo=@CategorySysNo #DynamicParameters#，那么DataAccess层中的代码应该如下：
        ///  cmd.SetParameter("@CategorySysNo", DbType.Int32, categorySysNo);
        ///  cmd.SetDynamicParameter("ProductStatus", ConditionOperation.In, DbType.Int32, pstatusList);
        ///  cmd.ExecuteScalar<T>("#DynamicParameters#");
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dynamicParameters"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string dynamicParameters)
        {
            ParseDynamicParametersForExecute(dynamicParameters);
            return ExecuteScalar<T>();
        }

        /// <summary>
        /// 标准方法：执行无返回
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery()
        {
            SQLiteCommand cmd = GetConnection().CreateCommand();
            DataTable table = new DataTable();
            ConnectionWrapper<DbConnection> wrapper = null;
            try
            {
                wrapper = TransactionScopeConnections.GetOpenConnection(m_ConnStr, GetConnection, true);
                PrepareCommand(cmd, wrapper.Connection, null, CommandType.Text, m_CommandText, TIMEOUT, m_DbParameterList.ToArray());

                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                throw new Exception("ExecuteScalar Error:" + ex.Message);
            }
            finally
            {
                if (wrapper != null)
                {
                    wrapper.Dispose();
                }
            }

        }

        /// <summary>
        /// 标准方法：执行无返回;
        /// 含有动态条件的执行，如sql：WHERE CategorySysNo=@CategorySysNo #DynamicParameters#，那么DataAccess层中的代码应该如下：
        ///  cmd.SetParameter("@CategorySysNo", DbType.Int32, categorySysNo);
        ///  cmd.SetDynamicParameter("ProductStatus", ConditionOperation.In, DbType.Int32, pstatusList);
        ///  cmd.ExecuteNonQuery("#DynamicParameters#");
        /// </summary>
        /// <param name="dynamicParameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string dynamicParameters)
        {
            ParseDynamicParametersForExecute(dynamicParameters);
            return ExecuteNonQuery();
        }

        #endregion

        #region 查询专用
        /// <summary>
        /// 查询，返回条件页的DataTable；
        /// 查询的SQL语句有两个，第一个返回总的数量，第二个返回数据结果集；
        /// 查询条件可以使用参数，也可以自己定义拼装，注意拼装时，对参数值都使用SetSafeParameter(string parameterValue)处理一下。
        /// </summary>
        /// <param name="filter">继承QueryFilter的查询条件对象</param>
        /// <returns></returns>
        public QueryResult Query(QueryFilter filter, string defaultSortBy)
        {
            DataSet ds = _queryExecute(filter, defaultSortBy);
            int count = int.Parse(ds.Tables[0].Rows[0][0].ToString());

            QueryResult result = new QueryResult();
            result.data = ds.Tables[1];
            result.PageIndex = filter.PageIndex;
            result.PageSize = filter.PageSize;
            result.SortFields = filter.SortFields;
            result.recordsTotal = count;
            return result;
        }

        /// <summary>
        /// 查询，返回条件页的实体列表
        /// 查询的SQL语句有两个，第一个返回总的数量，第二个返回数据结果集
        /// 查询条件可以使用参数，也可以自己定义拼装，注意拼装时，对参数值都使用SetSafeParameter(string parameterValue)处理一下。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filter"></param>
        /// <returns></returns>
        public QueryResult<T> Query<T>(QueryFilter filter, string defaultSortBy, Action<DataRow, T> manualMapper = null) where T : class, new()
        {
            DataSet ds = _queryExecute(filter, defaultSortBy);
            int count = int.Parse(ds.Tables[0].Rows[0][0].ToString());

            QueryResult<T> result = new QueryResult<T>();
            result.PageIndex = filter.PageIndex;
            result.PageSize = filter.PageSize;
            result.SortFields = filter.SortFields;
            result.draw = filter.draw;
            result.recordsTotal = count;

            if (ds.Tables[1] != null && ds.Tables[1].Rows != null && ds.Tables[1].Rows.Count > 0)
            {
                result.data = DataMapper.GetEntityList<T, List<T>>(ds.Tables[1].Rows, true, true, manualMapper);
            }
            else
            {
                result.data = new List<T>();
            }

            if (ds.Tables.Count >= 3)
            {
                result.Summary = ds.Tables[2].Rows[0][0] != null ? ds.Tables[2].Rows[0][0].ToString().Trim() : "";
            }

            return result;
        }

        public DataSet _queryExecute(QueryFilter filter, string defaultSortBy)
        {
            if (string.IsNullOrWhiteSpace(defaultSortBy))
            {
                throw new Exception("Default SortBy can not be empty!");
            }
            if (string.IsNullOrWhiteSpace(filter.SortFields))
            {
                filter.SortFields = defaultSortBy;
            }
            m_CommandText = m_CommandText.Replace("@SortFields", filter.SortFields);
            m_CommandText = m_CommandText.Replace("@PageSize", filter.PageSize.ToString());
            m_CommandText = m_CommandText.Replace("@PageIndex", filter.PageIndex.ToString());

            m_CommandText = m_CommandText.Replace("#STRWHERE#", m_QueryConditionString);

            DataSet ds = ExecuteDataSet();
            if (ds.Tables.Count < 2)
            {
                throw new Exception("Query SQL Script is Error, it should return 2 tables, 1st table is record count, 2rd table is dataresult.");
            }
            int count = 0;
            if (ds.Tables[0].Rows[0][0] == null || !int.TryParse(ds.Tables[0].Rows[0][0].ToString(), out count))
            {
                throw new Exception("Query SQL Script is Error, 1st table is record count, it must be integer.");
            }
            return ds;


        }


        private string m_QueryConditionString = INIT_QUERYCONDITION_STRING;
        public string QueryConditionString
        {
            get
            {
                return m_QueryConditionString;
            }
            set
            {
                m_QueryConditionString = value;
            }
        }

        /// <summary>
        /// 设置查询参数，都是AND模式，如果值为空，将自动不作为条件
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="condOperation"></param>
        /// <param name="parameterDbType"></param>
        /// <param name="objValue"></param>
        public void QuerySetCondition(string fieldName, ConditionOperation condOperation, DbType parameterDbType, object objValue)
        {
            if (objValue == null)
            {
                return;
            }
            if (string.IsNullOrWhiteSpace(objValue.ToString()))
            {
                return;
            }

            string strCond = "";
            switch (condOperation)
            {
                case ConditionOperation.Equal:
                    strCond = string.Format(" AND {0}={1}", fieldName, _buildConditionValue(parameterDbType, objValue));
                    break;
                case ConditionOperation.NotEqual:
                    strCond = string.Format(" AND {0}<>{1}", fieldName, _buildConditionValue(parameterDbType, objValue));
                    break;
                case ConditionOperation.LessThan:
                    strCond = string.Format(" AND {0}<{1}", fieldName, _buildConditionValue(parameterDbType, objValue));
                    break;
                case ConditionOperation.LessThanEqual:
                    strCond = string.Format(" AND {0}<={1}", fieldName, _buildConditionValue(parameterDbType, objValue));
                    break;
                case ConditionOperation.MoreThan:
                    strCond = string.Format(" AND {0}>{1}", fieldName, _buildConditionValue(parameterDbType, objValue));
                    break;
                case ConditionOperation.MoreThanEqual:
                    strCond = string.Format(" AND {0}>={1}", fieldName, _buildConditionValue(parameterDbType, objValue));
                    break;
                case ConditionOperation.Like:
                    strCond = string.Format(" AND {0} LIKE {1}", fieldName, "'%" + SetSafeParameter(objValue.ToString()) + "%'");
                    break;
                case ConditionOperation.LikeLeft:
                    strCond = string.Format(" AND {0} LIKE {1}", fieldName, "'" + SetSafeParameter(objValue.ToString()) + "%'");
                    break;
                case ConditionOperation.LikeRight:
                    strCond = string.Format(" AND {0} LIKE {1}", fieldName, "'%" + SetSafeParameter(objValue.ToString()) + "'");
                    break;
                case ConditionOperation.In:
                    if (typeof(IList).IsAssignableFrom(objValue.GetType()))
                    {
                        if (objValue is List<int> || objValue is List<Int16> || objValue is List<Int32> || objValue is List<Int64>
                            || objValue is List<uint> || objValue is List<UInt16> || objValue is List<UInt32> || objValue is List<UInt64>
                            || objValue is List<decimal> || objValue is List<float> || objValue is List<long> || objValue is List<double>)
                        {
                            string lstStr = string.Empty;
                            foreach (var x in (IEnumerable)objValue)
                            {
                                lstStr += x.ToString() + ",";
                            }
                            if (!string.IsNullOrEmpty(lstStr))
                            {
                                lstStr = lstStr.TrimEnd(',');
                            }
                            if (!string.IsNullOrEmpty(lstStr))
                            {
                                strCond = string.Format(" AND {0} IN ({1})", fieldName, lstStr);
                            }
                        }
                        else
                        {
                            string lstStr = string.Empty;
                            foreach (var x in (IEnumerable)objValue)
                            {
                                lstStr += "'" + SetSafeParameter(x.ToString()) + "',";
                            }
                            if (!string.IsNullOrEmpty(lstStr))
                            {
                                lstStr = lstStr.TrimEnd(',');
                            }
                            if (!string.IsNullOrEmpty(lstStr))
                            {
                                strCond = string.Format(" AND {0} IN ({1})", fieldName, lstStr);
                            }
                        }
                    }
                    else
                    {
                        strCond = string.Format(" AND {0} IN ({1})", fieldName, objValue.ToString());
                    }
                    break;
                case ConditionOperation.NotIn:
                    if (typeof(IList).IsAssignableFrom(objValue.GetType()))
                    {
                        if (objValue is List<int> || objValue is List<Int16> || objValue is List<Int32> || objValue is List<Int64>
                            || objValue is List<uint> || objValue is List<UInt16> || objValue is List<UInt32> || objValue is List<UInt64>
                            || objValue is List<decimal> || objValue is List<float> || objValue is List<long> || objValue is List<double>)
                        {
                            string lstStr = string.Empty;
                            foreach (var x in (IEnumerable)objValue)
                            {
                                lstStr += x.ToString() + ",";
                            }
                            if (!string.IsNullOrEmpty(lstStr))
                            {
                                lstStr = lstStr.TrimEnd(',');
                            }
                            if (!string.IsNullOrEmpty(lstStr))
                            {
                                strCond = string.Format(" AND {0} NOT IN ({1})", fieldName, lstStr);
                            }
                        }
                        else
                        {
                            string lstStr = string.Empty;
                            foreach (var x in (IEnumerable)objValue)
                            {
                                lstStr += "'" + SetSafeParameter(x.ToString()) + "',";
                            }
                            if (!string.IsNullOrEmpty(lstStr))
                            {
                                lstStr = lstStr.TrimEnd(',');
                            }
                            if (!string.IsNullOrEmpty(lstStr))
                            {
                                strCond = string.Format(" AND {0} NOT IN ({1})", fieldName, lstStr);
                            }
                        }
                    }
                    else
                    {
                        strCond = string.Format(" AND {0} NOT IN ({1})", fieldName, objValue.ToString());
                    }

                    break;
                default:
                    strCond = string.Format(" AND {0}={1}", fieldName, _buildConditionValue(parameterDbType, objValue));
                    break;
            }
            m_QueryConditionString += strCond;


        }



        /// <summary>
        /// 设置自定义查询参数，模式由查询条件指定
        /// </summary>
        /// <param name="customerQueryCondition"></param>
        public void QuerySetCondition(string customerQueryCondition)
        {
            if (string.IsNullOrWhiteSpace(customerQueryCondition))
            {
                return;
            }
            m_QueryConditionString += " " + customerQueryCondition;
        }

        private string _buildConditionValue(DbType parameterDbType, object objValue)
        {
            if (objValue.GetType().IsEnum)
            {
                if (objValue.GetType().GetEnumUnderlyingType() == typeof(byte))
                {
                    objValue = (byte)objValue;
                }
                else
                {
                    objValue = (int)objValue;
                }
            }
            switch (parameterDbType)
            {
                case DbType.Int16:
                case DbType.Int32:
                case DbType.Int64:
                case DbType.Single:
                case DbType.Decimal:
                case DbType.Double:
                case DbType.DateTimeOffset:
                case DbType.SByte:
                case DbType.Byte:
                case DbType.Time:
                case DbType.UInt16:
                case DbType.UInt32:
                case DbType.UInt64:
                case DbType.VarNumeric:
                    return SetSafeParameter(objValue.ToString());
                default:
                    return "'" + SetSafeParameter(objValue.ToString()) + "'";
            }

        }

        /// <summary>
        /// 设置安全参数值，防注入攻击；尤其是在拼装SQL 查询语句时需要
        /// </summary>
        /// <param name="parameterValue"></param>
        /// <returns></returns>
        public string SetSafeParameter(string parameterValue)
        {
            string v = parameterValue.Replace("'", "''");
            return v;
        }
        #endregion

    }

    public enum ConditionOperation
    {
        Equal,
        NotEqual,
        MoreThan,
        MoreThanEqual,
        LessThan,
        LessThanEqual,
        Like,
        LikeRight,
        LikeLeft,
        In,
        NotIn
    }
}
